ALTER PROC [dbo].[SP_GET_CHI_TIEU]
@NGUOI_CHI INT, 
@NGAY_CHI_FROM DATE, 
@NGAY_CHI_TO DATE,
@SO_THUE_PHONG INT, 
@TINH_TRANG INT 
AS
BEGIN
	SELECT 
		CHI_TIEU.ID, 
		MUC_DICH, 
		ISNULL(SO_TIEN,0) SO_TIEN, 
		NGUOI_CHI, 
		NGAY_CHI,
		USR.FULL_NAME, 
		PHONG.SO_PHONG, 
		KHACH_THUE.HO_VA_TEN, 
		CHI_TIEU.MA_PHIEU_THU
	FROM 
		CHI_TIEU
		LEFT JOIN THUE_PHONG ON CHI_TIEU.SO_THUE_PHONG = THUE_PHONG.ID
		LEFT JOIN PHONG ON THUE_PHONG.MA_PHONG = PHONG.ID
		LEFT JOIN KHACH_THUE ON KHACH_THUE.SO_THUE_PHONG = THUE_PHONG.ID AND KHACH_THUE.LEVEL = 1
		JOIN USR ON CHI_TIEU.NGUOI_CHI = USR.ID
	WHERE 
		    (@NGUOI_CHI = 0 OR CHI_TIEU.NGUOI_CHI = @NGUOI_CHI)
		AND (@NGAY_CHI_FROM IS NULL OR CAST(CHI_TIEU.NGAY_CHI AS DATE) >= CAST(@NGAY_CHI_FROM AS DATE))
		AND (@NGAY_CHI_TO IS NULL OR CAST(CHI_TIEU.NGAY_CHI AS DATE) <= CAST(@NGAY_CHI_TO AS DATE))
		AND (@SO_THUE_PHONG = -1 OR (@SO_THUE_PHONG = 0 AND CHI_TIEU.SO_THUE_PHONG IS NOT NULL) OR (CHI_TIEU.SO_THUE_PHONG = @SO_THUE_PHONG))
		AND ((@TINH_TRANG = 0 AND CHI_TIEU.ACTIVE IN ('A','C'))
				OR (@TINH_TRANG = 1 AND CHI_TIEU.ACTIVE = 'A' AND CHI_TIEU.SO_THUE_PHONG IS NOT NULL) 
				OR (@TINH_TRANG = 2 AND CHI_TIEU.ACTIVE = 'C' AND CHI_TIEU.SO_THUE_PHONG IS NOT NULL))
	ORDER BY NGAY_CHI DESC
END

ALTER PROC [dbo].[SP_GET_CUS_HIST]
@ID INT
AS
BEGIN
	SELECT 
	THU_TIEN.ID,
	NGAY_THU, 
	NGUOI_THU, 
	FULL_NAME, 
	(ISNULL(CHI_SO_NUOC_SAU,0) - ISNULL(CHI_SO_NUOC_TRUOC,0))*ISNULL(DON_GIA_NUOC,0) TIEN_NUOC, 
	(ISNULL(CHI_SO_DIEN_SAU,0) - ISNULL(CHI_SO_DIEN_TRUOC,0))*ISNULL(DON_GIA_DIEN,0) TIEN_DIEN, 
	ISNULL(TIEN_PHONG, 0) TIEN_PHONG,
	ISNULL(SO_THANG, 0) SO_THANG,
	ISNULL(TIEN_PHU_THU,0) TIEN_PHU_THU, 
	DBO.F_GET_MAX_ID_OF_THU_TIEN(THU_TIEN.SO_THUE_PHONG) MAX_ID
	FROM 
	THU_TIEN, 
	USR
	WHERE
	THU_TIEN.ACTIVE = 'A'
	AND THU_TIEN.NGUOI_THU = USR.ID
	AND THU_TIEN.SO_THUE_PHONG = @ID
	ORDER BY THU_TIEN.ID DESC
END

CREATE PROC SP_GET_CHI_TIEU_BY_ID 
@ID INT
AS
BEGIN
	SELECT 
		CHI_TIEU.ID, 
		CHI_TIEU.MUC_DICH, 
		CHI_TIEU.NGAY_CHI, 
		CHI_TIEU.NGUOI_CHI,
		CHI_TIEU.SO_THUE_PHONG, 
		CHI_TIEU.MA_PHIEU_THU, 
		CHI_TIEU.NOTES,
		USR.FULL_NAME, 
		CHI_TIEU.SO_TIEN
	FROM 
	CHI_TIEU, 
	USR
	WHERE CHI_TIEU.ACTIVE = 'A'
	AND CHI_TIEU.NGUOI_CHI = USR.ID
END

alter PROC SP_DELETE_CHI_TIEU
@ID INT, 
@USR_ID INT
AS
BEGIN
	UPDATE 
	CHI_TIEU 
	SET 
	ACTIVE = 'I',
	UPDATE_AT = GETDATE(),
	UPDATE_BY = @USR_ID
	WHERE ID = @ID
END


ALTER PROC SP_UPDATE_CHI_TIEU
@ID INT,
@MUC_DICH NVARCHAR(200), 
@SO_TIEN FLOAT, 
@NGAY_CHI DATE, 
@SO_THUE_PHONG INT, 
@NOTES NTEXT,
@USR_ID INT
AS
BEGIN
	UPDATE CHI_TIEU
	SET MUC_DICH = @MUC_DICH, 
	SO_TIEN = @SO_TIEN, 
	NGAY_CHI = @NGAY_CHI, 
	SO_THUE_PHONG = 
	CASE @SO_THUE_PHONG 
	WHEN 0 THEN 
		 NULL
	ELSE @SO_THUE_PHONG END, 
	UPDATE_AT = GETDATE(), 
	UPDATE_BY = @USR_ID, 
	NOTES = @NOTES
	WHERE  ID = @ID
END



CREATE VIEW V_MONEY_REPORT
AS
SELECT 
	THU_TIEN.NGUOI_THU, 
	THU_TIEN.NGAY_THU,
	SUM(ISNULL(THU_TIEN.TIEN_PHONG,0)) TIEN_PHONG, 
	SUM(ISNULL(THU_TIEN.TIEN_PHU_THU,0)) PHU_THU,
	SUM((ISNULL(THU_TIEN.CHI_SO_DIEN_SAU,0) - ISNULL(THU_TIEN.CHI_SO_DIEN_TRUOC,0))*ISNULL(THU_TIEN.DON_GIA_DIEN,0)) TIEN_DIEN, 
	SUM((ISNULL(THU_TIEN.CHI_SO_NUOC_SAU,0) - ISNULL(THU_TIEN.CHI_SO_NUOC_TRUOC,0))*ISNULL(THU_TIEN.DON_GIA_NUOC,0)) TIEN_NUOC, 
	0 THU_TIEN_COC, 
	0 TRA_TIEN_COC, 
	0 CHI_TIEU
FROM THU_TIEN
WHERE ACTIVE = 'A'
GROUP BY 
THU_TIEN.NGUOI_THU, 
THU_TIEN.NGAY_THU
UNION ALL
SELECT 
	CHI_TIEU.NGUOI_CHI, 
	CHI_TIEU.NGAY_CHI, 
	0 TIEN_NHA, 
	0 PHU_THU,
	0 TIEN_DIEN, 
	0 TIEN_NUOC, 
	0 THU_TIEN_COC, 
	0 TRA_TIEN_COC, 
	SUM(CHI_TIEU.SO_TIEN)
FROM CHI_TIEU
WHERE ACTIVE IN ('A','C')
GROUP BY 
CHI_TIEU.NGUOI_CHI, 
CHI_TIEU.NGAY_CHI
UNION ALL
SELECT 
	THUE_PHONG.CREATE_BY, 
	THUE_PHONG.NGAY_THUE, 
	0 TIEN_NHA, 
	0 PHU_THU,
	0 TIEN_DIEN, 
	0 TIEN_NUOC, 
	SUM(ISNULL(THUE_PHONG.TIEN_COC,0)), 
	0 TRA_TIEN_COC, 
	0 CHI_TIEU
FROM THUE_PHONG
WHERE ACTIVE = 'A'
GROUP BY 
THUE_PHONG.CREATE_BY, 
THUE_PHONG.NGAY_THUE
UNION ALL 
SELECT 
	THUE_PHONG.UPDATE_BY, 
	THUE_PHONG.NGAY_TRA, 
	0 TIEN_NHA, 
	0 PHU_THU,
	0 TIEN_DIEN, 
	0 TIEN_NUOC, 
	0 THU_TIEN_COC, 
	SUM(ISNULL(THUE_PHONG.TIEN_COC,0)), 
	0 CHI_TIEU
FROM THUE_PHONG
WHERE ACTIVE = 'A' AND THUE_PHONG.NGAY_TRA IS NOT NULL
GROUP BY 
THUE_PHONG.UPDATE_BY, 
THUE_PHONG.NGAY_TRA

ALTER PROC GET_DATA_REPORT
@USR_ID INT, 
@FROM_DATE DATE, 
@TO_DATE DATE
AS
BEGIN
 SELECT 
	 V_MONEY_REPORT.NGUOI_THU,
	 USR.FULL_NAME, 
	 V_MONEY_REPORT.NGAY_THU,
	 SUM(V_MONEY_REPORT.TIEN_PHONG) TIEN_PHONG, 
	 SUM(V_MONEY_REPORT.TIEN_DIEN) TIEN_DIEN, 
	 SUM(V_MONEY_REPORT.TIEN_NUOC) TIEN_NUOC, 
	 SUM(V_MONEY_REPORT.PHU_THU) PHU_THU, 
	 SUM(V_MONEY_REPORT.CHI_TIEU) CHI_TIEU, 
	 SUM(V_MONEY_REPORT.THU_TIEN_COC) THU_TIEN_COC, 
	 SUM(V_MONEY_REPORT.TRA_TIEN_COC) TRA_TIEN_COC, 
	 SUM(V_MONEY_REPORT.TIEN_PHONG) 
	 + SUM(V_MONEY_REPORT.TIEN_DIEN) 
	 + SUM(V_MONEY_REPORT.TIEN_NUOC) 
	 + SUM(V_MONEY_REPORT.PHU_THU) 
	 - SUM(V_MONEY_REPORT.CHI_TIEU)
	 + SUM(V_MONEY_REPORT.THU_TIEN_COC)
	 - SUM(V_MONEY_REPORT.TRA_TIEN_COC) TIEN_MAT
 FROM 
	V_MONEY_REPORT,
	USR
 WHERE 
	(@USR_ID = 0 OR V_MONEY_REPORT.NGUOI_THU = @USR_ID)
	AND (@FROM_DATE IS NULL OR CAST(V_MONEY_REPORT.NGAY_THU AS DATE) >= CAST(@FROM_DATE AS DATE))	
	AND (@TO_DATE IS NULL OR CAST(V_MONEY_REPORT.NGAY_THU AS DATE) <= CAST(@TO_DATE AS DATE))
	AND V_MONEY_REPORT.NGUOI_THU = USR.ID
 GROUP BY 	
 V_MONEY_REPORT.NGUOI_THU,
 USR.FULL_NAME, 
 V_MONEY_REPORT.NGAY_THU
END


ALTER TABLE dbo.PHONG
DROP COLUMN TINH_TRANG

ALTER PROC [dbo].[SP_INSERT_PHONG]
@SO_PHONG VARCHAR(10), 
@DON_GIA FLOAT, 
@DIEN_TICH FLOAT, 
@GHI_CHU NVARCHAR(MAX),
@USER_ID INT
AS
BEGIN
	INSERT INTO PHONG
	(
		SO_PHONG, 
		DON_GIA, 
		DIEN_TICH, 
		GHI_CHU, 
		CREATE_BY,
		UPDATE_BY,
		CREATE_AT,
		UPDATE_AT, 
		ACTIVE
	)
	VALUES
	(
		@SO_PHONG , 
		@DON_GIA , 
		@DIEN_TICH , 
		@GHI_CHU,
		@USER_ID,
		@USER_ID,
		GETDATE(), 
		GETDATE(), 
		'A'
	)	
END

CREATE PROCEDURE [dbo].[SP_UPDATE_PHONG] 
	@SO_PHONG VARCHAR(10), 
	@DON_GIA FLOAT, 
	@DIEN_TICH FLOAT, 
	@GHI_CHU NVARCHAR(MAX),
	@USER_ID INT,
	@ID INT
AS
BEGIN
	UPDATE PHONG
	   SET [SO_PHONG] = @SO_PHONG
		  ,[DON_GIA] = @DON_GIA
		  ,[DIEN_TICH] = @DIEN_TICH
		  ,[GHI_CHU] = @GHI_CHU
		  ,[UPDATE_BY] = @USER_ID
		  ,[UPDATE_AT] = GETDATE()
	 WHERE [ID] = @ID
END


CREATE PROCEDURE [dbo].[SP_DELETE_PHONG] 
	@id int
AS
BEGIN
	UPDATE PHONG
	SET ACTIVE = 'I'
	WHERE ID = @id
END


CREATE FUNCTION F_GET_CONFIG
(@NAME VARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN (SELECT TOP 1 MASTER.VAL FROM MASTER WHERE NAME = @NAME AND ACTIVE = 'A')
END


CREATE PROC SP_UPDATE_CONFIG
@NAME VARCHAR(100), 
@VAL NVARCHAR(1000), 
@USR_ID INT
AS
BEGIN
	UPDATE MASTER
	SET VAL = @VAL, 
	UPDATE_AT = GETDATE(), 
	UPDATE_BY =  @USR_ID
	WHERE NAME = @NAME AND ACTIVE = 'A'
END

CREATE PROC SP_SAVE_CONFIG
@NAME VARCHAR(100), 
@VAL NVARCHAR(1000), 
@USR_ID INT
AS
BEGIN
	DECLARE @COUNT INT;
	SET @COUNT = (SELECT COUNT(ID) FROM MASTER WHERE ACTIVE = 'A' AND NAME = @NAME);
	IF @COUNT = 0
	BEGIN
		INSERT INTO MASTER
		(
			NAME, 
			VAL, 
			CREATE_AT,
			UPDATE_AT,
			CREATE_BY, 
			UPDATE_BY, 
			ACTIVE
		)
		VALUES
		(
			@NAME, 
			@VAL, 
			GETDATE(), 
			GETDATE(), 
			@USR_ID, 
			@USR_ID, 
			'A'
		)
	END 
	ELSE
	BEGIN
		EXEC DBO.SP_UPDATE_CONFIG @NAME, @VAL, @USR_ID
	END
END

CREATE PROC SP_GET_CONFIG
AS
BEGIN
	SELECT 
	DBO.F_GET_CONFIG('ELE_PRICE') ELE_PRICE, 
	DBO.F_GET_CONFIG('WAT_PRICE') WAT_PRICE,
	DBO.F_GET_CONFIG('EMAIL') EMAIL,
	DBO.F_GET_CONFIG('PASS') PASS
END


ALTER PROC [dbo].[SP_GET_PHONG]
@SO_PHONG INT,
@TINH_TRANG INT
AS
BEGIN
	SELECT 
		ID,
		SO_PHONG,
		ISNULL(DON_GIA,0) DON_GIA, 
		ISNULL(DIEN_TICH,0) DIEN_TICH, 
		GHI_CHU, 
		DBO.F_GET_ROOM_STATUS(ID) TINH_TRANG
	FROM 
	PHONG
	WHERE
	(@SO_PHONG = 0 OR PHONG.SO_PHONG = @SO_PHONG)
	AND (@TINH_TRANG = 0 OR 
		(@TINH_TRANG = 1 AND PHONG.ID NOT IN (SELECT MA_PHONG FROM THUE_PHONG WHERE ACTIVE = 'A' AND NGAY_TRA IS NULL)) OR
		(@TINH_TRANG = 2 AND PHONG.ID IN (SELECT MA_PHONG FROM THUE_PHONG WHERE ACTIVE = 'A' AND NGAY_TRA IS NULL)))
	AND PHONG.ACTIVE = 'A'
	ORDER BY DBO.F_GET_ROOM_STATUS(ID) 
END
